﻿CREATE PROCEDURE [dbo].[proc_FinanceMonthTable_PZ_Getlist]
	(
		@CompanyId VARCHAR(500),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex int
	)
AS
BEGIN
	DECLARE @strSql VARCHAR(4000)
	set @strSql=''
	if @CompanyId<>'0'
	begin
		set @strSql=' CompanyId in ('+@CompanyId+') and '
	end
	if @Stext<>''
	begin
		set @strSql=@strSql+'Reason like ''%'+@Stext+'%'' and '
	end
	SET @strSql='
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, '''+@StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime, '''+@EndDate + ' 23:59:59'')
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY CreateDate desc)AS Row,
		[id]
      ,[GId]
      ,[Companyid]
      ,[CompanyName]
      ,[TypeId]
      ,[CateTwoId]
      ,[SubjectId]
      ,[OrderType]
      ,[AccountType]
      ,[Money]
      ,[CreateDate]
      ,[userid]
      ,[username]
      ,[AuditUserid]
      ,[AuditUserName]
      ,[Reason]
		From [FinanceMonthTable_PZ]
		Where '+@strSql+'
		(CreateDate between @sRq and @eRq)
		
	)
	
	
	Select *,
	(case typeid when 1 then (select [Title] from [Finance_Subject_Parent] where [CateTwoId]=id)+''-''+(select [Title] from [Finance_Subject] where [SubjectId]=id) 
	when 2 then (select [name] from [Finance_RunningAccountType] where id=[AccountType]) 
	when 3 then ((case ordertype when 1 then ''新增成单额'' when 2 then ''续费成单额''  end)) end) as TypeName
	 From list,(Select Count(Id) AS c_id,Sum(Money) AS s_Prices From list
	) AS a Where Row between '+convert(varchar,@StartIndex)+' and '+convert(varchar,@EndIndex)+'  '
	-- PRINT(@strSql)
	 EXEC(@strSql)
end
	RETURN
